-- create "MDRU"."SND_BASE_DOCS" table
CREATE TABLE "MDRU"."SND_BASE_DOCS" (
  "ID" NUMBER NOT NULL ENABLE,
	"DOCUMENT_ID" VARCHAR2(36) NOT NULL ENABLE, 
	"REF_DOCUMENT_ID" VARCHAR2(36), 
	"TOTAL_TRANSPORT_CROSS_BORDER" NUMBER, 
	"OFFICE_ON_BORDER" NUMBER, 
	"CONTRACT_NO" VARCHAR2(50), 
	"DOC_KIND" VARCHAR2(50), 
	"COUNTRY_DISPATCH" VARCHAR2(2), 
	"COUNTRY_DESTINATION" VARCHAR2(2), 
	"ITEMS" NUMBER, 
	"COUNTRY_ORIGIN" VARCHAR2(2), 
	"CURRENCY" NUMBER, 
	"EXCHANGE_RATE" NUMBER, 
	"TOTAL_AMOUNT_INVOICED" NUMBER, 
	"DOC_NUMBER" VARCHAR2(30), 
	"CUST_CODE" NUMBER, 
	"DIRECTION" VARCHAR2(200) NOT NULL ENABLE, 
	"TIR_PAGE_NO" NUMBER, 
	"PROCEDURE" NUMBER, 
	"DOC_DATE" DATE NOT NULL ENABLE, 
	"CARRIER_INN" NUMBER, 
	"CARRIER_NAME" VARCHAR2(80), 
	"CARRIER_ADDRESS" VARCHAR2(80), 
	"CONSIGNOR_NAME" VARCHAR2(80), 
	"CONSIGNOR_INN" NUMBER, 
	"CONSIGNOR_ADDRESS" VARCHAR2(80), 
	"CONSIGNEE_NAME" VARCHAR2(80), 
	"CONSIGNEE_INN" NUMBER, 
	"CONSIGNEE_ADDRESS" VARCHAR2(80), 
	"DRIVER_PERSON_DATA" VARCHAR2(250), 
	"UCR_NO_CUST_CODE" NUMBER, 
	"UCR_NO" NUMBER, 
	"UCR_NO_DATE_OUT" DATE, 
	 PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE "MDRU"  ENABLE
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
  TABLESPACE "MDRU";

-- create MDRU.SND_BASE_DOCS_ID_SEQUENCE sequence
CREATE SEQUENCE  "MDRU"."SND_BASE_DOCS_ID_SEQUENCE"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  INCREMENT BY 1
  START WITH 1
  NOCACHE
  NOORDER
  NOCYCLE;

-- create "MDRU"."RCV_BASE_DOCS" table
CREATE TABLE "MDRU"."RCV_BASE_DOCS" (
  "ID" NUMBER NOT NULL ENABLE,
  "DOCUMENT_ID" VARCHAR2(36) NOT NULL ENABLE,
  "REF_DOCUMENT_ID" VARCHAR2(36),
  "TOTAL_TRANSPORT_CROSS_BORDER" NUMBER,
  "OFFICE_ON_BORDER" NUMBER,
  "CONTRACT_NO" VARCHAR2(50),
  "DOC_KIND" VARCHAR2(50),
  "COUNTRY_DISPATCH" VARCHAR2(2),
  "COUNTRY_DESTINATION" VARCHAR2(2),
  "ITEMS" NUMBER,
  "COUNTRY_ORIGIN" VARCHAR2(2),
  "CURRENCY" NUMBER,
  "EXCHANGE_RATE" NUMBER,
  "TOTAL_AMOUNT_INVOICED" NUMBER,
  "DOC_NUMBER" VARCHAR2(30),
  "CUST_CODE" NUMBER,
  "DIRECTION" VARCHAR2(200) NOT NULL ENABLE,
  "TIR_PAGE_NO" NUMBER,
  "PROCEDURE" NUMBER,
  "DOC_DATE" DATE NOT NULL ENABLE,
  "CARRIER_INN" NUMBER,
  "CARRIER_NAME" VARCHAR2(80),
  "CARRIER_ADDRESS" VARCHAR2(80),
  "CONSIGNOR_NAME" VARCHAR2(80),
  "CONSIGNOR_INN" NUMBER,
  "CONSIGNOR_ADDRESS" VARCHAR2(80),
  "CONSIGNEE_NAME" VARCHAR2(80),
  "CONSIGNEE_INN" NUMBER,
  "CONSIGNEE_ADDRESS" VARCHAR2(80),
  "DRIVER_PERSON_DATA" VARCHAR2(250),
  "UCR_NO_CUST_CODE" NUMBER,
  "UCR_NO" NUMBER,
  "UCR_NO_DATE_OUT" DATE,
  "TOTAL_PACKAGES" NUMBER,
   PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "MDRU"  ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  NOCOMPRESS LOGGING
  TABLESPACE "MDRU";

-- create SEQUENCE MDRU.CRV_BASE_DOCS_ID_SEQUENCE sequence
CREATE SEQUENCE  "MDRU"."CRV_BASE_DOCS_ID_SEQUENCE"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  INCREMENT BY 1
  START WITH 1
  NOCACHE
  NOORDER
  NOCYCLE;

-- create "MDRU"."GOODS" table
CREATE TABLE "MDRU"."SND_GOODS" (
  "ID" NUMBER NOT NULL ENABLE,
	"number" NUMBER,
	"NET_MASS" NUMBER,
	"GROSS_MASS" NUMBER,
	"COUNTRY_ORIGIN" VARCHAR2(2),
	"CURRENCY" NUMBER,
	"INVOICE_VALUE" NUMBER,
	"STAT_VALUE" NUMBER,
	"PACKAGES_QUANTITY" NUMBER,
	"CONTAINER_QUANTITY" NUMBER,
	"CONTAINER_NO" NUMBER,
	"DESCRIPTION" VARCHAR2(250) NOT NULL ENABLE,
	"CODE" NUMBER,
	"QUANTITY_SUP_UNIT" NUMBER,
	"UNIT_QUANTITY_CODE" NUMBER,
	"UNIT_QUANTITY_NAME" VARCHAR2(255),
	"BASE_DOC_ID" NUMBER NOT NULL ENABLE,
	 PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "MDRU"  ENABLE,
	 FOREIGN KEY ("BASE_DOC_ID")
	  REFERENCES "MDRU"."SND_BASE_DOCS" ("ID") ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "MDRU";

-- create SEQUENCE MDRU.SND_GOODS_ID_SEQUENCE sequence
CREATE SEQUENCE  "MDRU"."SND_GOODS_ID_SEQUENCE"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  INCREMENT BY 1
  START WITH 1
  NOCACHE
  NOORDER
  NOCYCLE;

-- create "MDRU"."RCV_GOODS" table
CREATE TABLE "MDRU"."RCV_GOODS" (
  "ID" NUMBER NOT NULL ENABLE,
	"number" NUMBER,
	"NET_MASS" NUMBER,
	"GROSS_MASS" NUMBER,
	"COUNTRY_ORIGIN" VARCHAR2(2),
	"CURRENCY" NUMBER,
	"INVOICE_VALUE" NUMBER,
	"STAT_VALUE" NUMBER,
	"PACKAGES_QUANTITY" NUMBER,
	"CONTAINER_QUANTITY" NUMBER,
	"CONTAINER_NO" NUMBER,
	"DESCRIPTION" VARCHAR2(250) NOT NULL ENABLE,
	"CODE" NUMBER NOT NULL ENABLE,
	"QUANTITY_SUP_UNIT" NUMBER,
	"UNIT_QUANTITY_CODE" NUMBER,
	"UNIT_QUANTITY_NAME" VARCHAR2(255),
	"BASE_DOC_ID" NUMBER NOT NULL ENABLE,
	 PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "MDRU"  ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  NOCOMPRESS LOGGING
  TABLESPACE "MDRU";

-- create "MDRU"."RCV_GOODS_ID_SEQUENCE" sequence
CREATE SEQUENCE  "MDRU"."RCV_GOODS_ID_SEQUENCE"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  INCREMENT BY 1
  START WITH 1
  NOCACHE
  NOORDER
  NOCYCLE;

-- create "MDRU"."DOCUMENTS" table
CREATE TABLE "MDRU"."DOCUMENTS" (
  "ID" NUMBER NOT NULL ENABLE,
  "DOC_NUMBER" VARCHAR2(30),
  "DOC_DATE" DATE,
  "DOC_KIND" VARCHAR2(50),
  "TMMASS" NUMBER,
  "BASE_DOC_ID" NUMBER,
  "GOODS_ID" NUMBER,
  "DOC_TYPE" VARCHAR2(25) NOT NULL ENABLE,
   PRIMARY KEY ("ID")
   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
   TABLESPACE "MDRU"  ENABLE,
   CHECK (doc_type in ('A', 'B')) ENABLE
    ) SEGMENT CREATION DEFERRED
   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  NOCOMPRESS LOGGING
  TABLESPACE "MDRU";

-- create SEQUENCE MDRU.DOCUMENTS_ID_SEQUENCE sequence
CREATE SEQUENCE  "MDRU"."DOCUMENTS_ID_SEQUENCE"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  INCREMENT BY 1
  START WITH 1
  NOCACHE
  NOORDER
  NOCYCLE;

-- create "MDRU"."TRANSPORTS" table
CREATE TABLE "MDRU"."TRANSPORTS" (
  "ID" NUMBER NOT NULL ENABLE,
  "BASE_DOC_ID" NUMBER NOT NULL ENABLE,
  "DEPARTURE_IDENTIFIER" VARCHAR2(30),
  "CROSS_BORDER_IDENTIFIER" VARCHAR2(30),
  "CROSS_BORDER_TMMAS" VARCHAR2(19) NOT NULL ENABLE,
   PRIMARY KEY ("ID")
   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "MDRU"  ENABLE
    ) SEGMENT CREATION IMMEDIATE
   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  NOCOMPRESS LOGGING
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "MDRU";

-- create SEQUENCE MDRU.TRANSPORTS_ID_SEQUENCE seuence
CREATE SEQUENCE  "MDRU"."TRANSPORTS_ID_SEQUENCE"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  INCREMENT BY 1
  START WITH 2
  NOCACHE
  NOORDER
  NOCYCLE;

-- create "MDRU"."TRANSACTION_TYPES" table
CREATE TABLE "MDRU"."TRANSACTION_TYPES" (
  "ID" NUMBER NOT NULL ENABLE,
	"NAME" VARCHAR2(100) NOT NULL ENABLE,
	"DESCRIPTION" VARCHAR2(300),
	 PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MDRU"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MDRU";

-- create SEQUENCE MDRU.TRANSACTIONS_ID_SEQUENCE sequence
CREATE SEQUENCE  "MDRU"."TRANSACTIONS_ID_SEQUENCE"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  INCREMENT BY 1
  START WITH 1
  NOCACHE
  NOORDER
  NOCYCLE;

-- create "MDRU"."TRANSACTION_INFOS" table
CREATE TABLE "MDRU"."TRANSACTION_INFOS" (
  "ID" NUMBER NOT NULL ENABLE,
	PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "MDRU"  ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  NOCOMPRESS LOGGING
  TABLESPACE "MDRU";

-- create SEQUENCE MDRU.TRANSACTION_INFOS_ID_SEQUENCE sequence
CREATE SEQUENCE  "MDRU"."TRANSACTION_INFOS_ID_SEQUENCE"
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  INCREMENT BY 1
  START WITH 1
  NOCACHE
  NOORDER
  NOCYCLE;




--  create procedure to insert goods from confirmation templates
CREATE OR REPLACE PROCEDURE insert_base_doc_conf_data (
    "INDEX" IN VARCHAR2, "BASE_DOC_ID" IN "MDRU"."SND_BASE_DOCS"."ID"%TYPE,
    "DOCUMENT_ID" IN "MDRU"."SND_BASE_DOCS"."DOCUMENT_ID"%TYPE, "REF_DOCUMENT_ID" IN "MDRU"."SND_BASE_DOCS"."REF_DOCUMENT_ID"%TYPE,
    "TOTAL_PACKAGES" IN "MDRU"."SND_BASE_DOCS"."TOTAL_PACKAGES"%TYPE, "TOTAL_TRANSPORT_CROSS_BORDER" IN "MDRU"."SND_BASE_DOCS"."TOTAL_TRANSPORT_CROSS_BORDER"%TYPE,
    "CONTRACT_NO" IN "MDRU"."SND_BASE_DOCS"."CONTRACT_NO"%TYPE, "OFFICE_ON_BORDER" IN "MDRU"."SND_BASE_DOCS"."OFFICE_ON_BORDER"%TYPE,
    "DOC_KIND" IN "MDRU"."SND_BASE_DOCS"."DOC_KIND"%TYPE, "COUNTRY_DISPATCH" IN "MDRU"."SND_BASE_DOCS"."COUNTRY_DISPATCH"%TYPE,
    "COUNTRY_DESTINATION" IN "MDRU"."SND_BASE_DOCS"."COUNTRY_DESTINATION"%TYPE, "DATE_ON_BORDER" IN "MDRU"."SND_BASE_DOCS"."DATE_ON_BORDER"%TYPE,
    "STAMP_NO" IN "MDRU"."SND_BASE_DOCS"."STAMP_NO"%TYPE, "STAMP_OFFICE" IN "MDRU"."SND_BASE_DOCS"."STAMP_OFFICE"%TYPE,
    "DOC_NUMBER" IN "MDRU"."SND_BASE_DOCS"."DOC_NUMBER"%TYPE, "CUST_CODE" IN "MDRU"."SND_BASE_DOCS"."CUST_CODE"%TYPE,
    "DOC_DATE" IN "MDRU"."SND_BASE_DOCS"."DOC_DATE"%TYPE,
    "TIR_PAGE_NO" IN "MDRU"."SND_BASE_DOCS"."TIR_PAGE_NO"%TYPE, "PROCEDURE" IN "MDRU"."SND_BASE_DOCS"."PROCEDURE"%TYPE,
    "UCR_NO_CUST_CODE" IN "MDRU"."SND_BASE_DOCS"."UCR_NO_CUST_CODE"%TYPE, "UCR_NO_DATE_OUT" IN "MDRU"."SND_BASE_DOCS"."UCR_NO_DATE_OUT"%TYPE,
    "UCR_NO" IN "MDRU"."SND_BASE_DOCS"."UCR_NO"%TYPE,
    "CARRIER_NAME" IN "MDRU"."SND_BASE_DOCS"."CARRIER_NAME"%TYPE, "CARRIER_INN" IN "MDRU"."SND_BASE_DOCS"."CARRIER_INN"%TYPE, "CARRIER_ADDRESS" IN "MDRU"."SND_BASE_DOCS"."CARRIER_ADDRESS"%TYPE,
    "DRIVER_PERSON_DATA" IN "MDRU"."SND_BASE_DOCS"."DRIVER_PERSON_DATA"%TYPE,
    "CONSIGNOR_NAME" IN "MDRU"."SND_BASE_DOCS"."CONSIGNOR_NAME"%TYPE, "CONSIGNOR_INN" IN "MDRU"."SND_BASE_DOCS"."CONSIGNOR_INN"%TYPE,
    "CONSIGNOR_ADDRESS" IN "MDRU"."SND_BASE_DOCS"."CONSIGNOR_ADDRESS"%TYPE,
    "CONSIGNEE_NAME" IN "MDRU"."SND_BASE_DOCS"."CONSIGNEE_NAME"%TYPE, "CONSIGNEE_INN" IN "MDRU"."SND_BASE_DOCS"."CONSIGNEE_INN"%TYPE,
    "CONSIGNEE_ADDRESS" IN "MDRU"."SND_BASE_DOCS"."CONSIGNEE_ADDRESS"%TYPE,
    "DELIVERY_PLACE" IN "MDRU"."SND_BASE_DOCS"."DELIVERY_PLACE"%TYPE, "DELIVERY_TERMS_NUMERIC_CODE" IN "MDRU"."SND_BASE_DOCS"."DELIVERY_TERMS_NUMERIC_CODE"%TYPE,
     "SAVED_FILE_NAME" IN "MDRU"."TRANSACTIONS"."FILE_PATH"%TYPE
)
IS
  TRANS_ID "MDRU"."TRANSACTIONS"."TRANSACTION_ID"%TYPE;
  TRANS_BASE_DOC_ID "MDRU"."SND_BASE_DOCS"."ID"%TYPE;
  TRANSACTION_TYPE VARCHAR2(6);
BEGIN

  SELECT t.transaction_id INTO TRANS_ID FROM "MDRU"."TRANSACTIONS" t WHERE t.guid_doc=REF_DOCUMENT_ID;

  IF "INDEX"='Arrive' THEN
    INSERT INTO "MDRU"."RCV_BASE_DOCS"(
        ID, DOCUMENT_ID, REF_DOCUMENT_ID, TOTAL_PACKAGES, TOTAL_TRANSPORT_CROSS_BORDER,
        CONTRACT_NO, OFFICE_ON_BORDER, DOC_KIND, COUNTRY_DISPATCH, COUNTRY_DESTINATION,
        DATE_ON_BORDER, STAMP_NO, STAMP_OFFICE, DOC_NUMBER, CUST_CODE, DOC_DATE,
        TIR_PAGE_NO, "PROCEDURE", UCR_NO_CUST_CODE, UCR_NO_DATE_OUT, UCR_NO,
        CARRIER_NAME, CARRIER_INN, CARRIER_ADDRESS, DRIVER_PERSON_DATA,
        CONSIGNOR_NAME, CONSIGNOR_INN, CONSIGNOR_ADDRESS, CONSIGNEE_NAME, CONSIGNEE_INN, CONSIGNEE_ADDRESS,
        DELIVERY_PLACE, DELIVERY_TERMS_NUMERIC_CODE
        )
    VALUES (
        BASE_DOC_ID, "DOCUMENT_ID", "REF_DOCUMENT_ID", "TOTAL_PACKAGES", "TOTAL_TRANSPORT_CROSS_BORDER",
        "CONTRACT_NO", "OFFICE_ON_BORDER", "DOC_KIND", "COUNTRY_DISPATCH", "COUNTRY_DESTINATION", "DATE_ON_BORDER",
        "STAMP_NO", "STAMP_OFFICE", "DOC_NUMBER", "CUST_CODE", "DOC_DATE", "TIR_PAGE_NO", "PROCEDURE",
        "UCR_NO_CUST_CODE", "UCR_NO_DATE_OUT", "UCR_NO", "CARRIER_NAME", "CARRIER_INN", "CARRIER_ADDRESS",
        "DRIVER_PERSON_DATA", "CONSIGNOR_NAME", "CONSIGNOR_INN", "CONSIGNOR_ADDRESS",
        "CONSIGNEE_NAME", "CONSIGNEE_INN", "CONSIGNEE_ADDRESS", "DELIVERY_PLACE", "DELIVERY_TERMS_NUMERIC_CODE");

    TRANSACTION_TYPE := 'RCV';

  ELSEIF "INDEX"='Depart' THEN
    INSERT INTO "MDRU"."RCV_BASE_DOCS"(
            ID, DOCUMENT_ID, REF_DOCUMENT_ID, TOTAL_PACKAGES, TOTAL_TRANSPORT_CROSS_BORDER,
            CONTRACT_NO, OFFICE_ON_BORDER, DOC_KIND, COUNTRY_DISPATCH, COUNTRY_DESTINATION,
            DATE_ON_BORDER, STAMP_NO, STAMP_OFFICE, DOC_NUMBER, CUST_CODE, DOC_DATE,
            TIR_PAGE_NO, "PROCEDURE", UCR_NO_CUST_CODE, UCR_NO_DATE_OUT, UCR_NO,
            CARRIER_NAME, CARRIER_INN, CARRIER_ADDRESS, DRIVER_PERSON_DATA,
            CONSIGNOR_NAME, CONSIGNOR_INN, CONSIGNOR_ADDRESS, CONSIGNEE_NAME, CONSIGNEE_INN, CONSIGNEE_ADDRESS,
            DELIVERY_PLACE, DELIVERY_TERMS_NUMERIC_CODE
            )
        VALUES (
            BASE_DOC_ID, "DOCUMENT_ID", "REF_DOCUMENT_ID", "TOTAL_PACKAGES", "TOTAL_TRANSPORT_CROSS_BORDER",
            "CONTRACT_NO", "OFFICE_ON_BORDER", "DOC_KIND", "COUNTRY_DISPATCH", "COUNTRY_DESTINATION", "DATE_ON_BORDER",
            "STAMP_NO", "STAMP_OFFICE", "DOC_NUMBER", "CUST_CODE", "DOC_DATE", "TIR_PAGE_NO", "PROCEDURE",
            "UCR_NO_CUST_CODE", "UCR_NO_DATE_OUT", "UCR_NO", "CARRIER_NAME", "CARRIER_INN", "CARRIER_ADDRESS",
            "DRIVER_PERSON_DATA", "CONSIGNOR_NAME", "CONSIGNOR_INN", "CONSIGNOR_ADDRESS",
            "CONSIGNEE_NAME", "CONSIGNEE_INN", "CONSIGNEE_ADDRESS", "DELIVERY_PLACE", "DELIVERY_TERMS_NUMERIC_CODE");

    TRANSACTION_TYPE := 'SND';

  ELSE
    RAISE CASE_NOT_FOUND;

  END IF;
  INSERT INTO "MDRU"."TRANSACTIONS" (
      id, transaction_id, type_id, table_id,
      guid_doc, guid_date, "date", file_path, direction
      )
      VALUES (
      MDRU.TRANSACTIONS_ID_SEQUENCE.NEXTVAL, TRANS_ID, 8, BASE_DOC_ID,
      DOCUMENT_ID, DOC_DATE, CURRENT_DATE, SAVED_FILE_NAME, TRANSACTION_TYPE
      );
END;


--  create procedure to insert goods from confirmation templates
CREATE OR REPLACE PROCEDURE insert_goods_conf_data (
    "BASE_DOC_INDEX" IN VARCHAR2, "GOODS_ID" IN "MDRU"."SND_GOODS"."ID"%TYPE,
    "NUMBER" IN "MDRU"."SND_GOODS"."number"%TYPE, "NET_MASS" IN "MDRU"."SND_GOODS"."NET_MASS"%TYPE,
    "GROSS_MASS" IN "MDRU"."SND_GOODS"."GROSS_MASS"%TYPE, "COUNTRY_ORIGIN" IN "MDRU"."SND_GOODS"."COUNTRY_ORIGIN"%TYPE,
    "CURRENCY" IN "MDRU"."SND_GOODS"."CURRENCY"%TYPE, "INVOICE_VALUE" IN "MDRU"."SND_GOODS"."INVOICE_VALUE"%TYPE,
    "STAT_VALUE" IN "MDRU"."SND_GOODS"."STAT_VALUE"%TYPE, "PACKAGES_QUANTITY" IN "MDRU"."SND_GOODS"."PACKAGES_QUANTITY"%TYPE,
    "CONTAINER_QUANTITY" IN "MDRU"."SND_GOODS"."CONTAINER_QUANTITY"%TYPE, "CONTAINER_NO" IN "MDRU"."SND_GOODS"."CONTAINER_NO"%TYPE,
    "DESCRIPTION" IN "MDRU"."SND_GOODS"."DESCRIPTION"%TYPE, "CODE" IN "MDRU"."SND_GOODS"."CODE"%TYPE,
    "QUANTITY_SUP_UNIT" IN "MDRU"."SND_GOODS"."QUANTITY_SUP_UNIT"%TYPE, "UNIT_QUANTITY_CODE" IN "MDRU"."SND_GOODS"."UNIT_QUANTITY_CODE"%TYPE,
    "UNIT_QUANTITY_NAME" IN "MDRU"."SND_GOODS"."UNIT_QUANTITY_NAME"%TYPE, "BASE_DOC_ID" IN "MDRU"."SND_BASE_DOCS"."ID"%TYPE
)
IS
BEGIN

  IF "BASE_DOC_INDEX"='Arrive' THEN
    INSERT INTO "MDRU"."RCV_GOODS"
      VALUES (GOODS_ID, "NUMBER", NET_MASS, GROSS_MASS, COUNTRY_ORIGIN, CURRENCY, INVOICE_VALUE, STAT_VALUE, PACKAGES_QUANTITY,
              CONTAINER_QUANTITY, CONTAINER_NO, DESCRIPTION, CODE, QUANTITY_SUP_UNIT, UNIT_QUANTITY_CODE, UNIT_QUANTITY_NAME, BASE_DOC_ID);


  ELSEIF "BASE_DOC_INDEX"='Depart' THEN
    INSERT INTO "MDRU"."SND_GOODS"
      VALUES (GOODS_ID, "NUMBER", NET_MASS, GROSS_MASS, COUNTRY_ORIGIN, CURRENCY, INVOICE_VALUE, STAT_VALUE, PACKAGES_QUANTITY,
              CONTAINER_QUANTITY, CONTAINER_NO, DESCRIPTION, CODE, QUANTITY_SUP_UNIT, UNIT_QUANTITY_CODE, UNIT_QUANTITY_NAME, BASE_DOC_ID);


  ELSE
    RAISE CASE_NOT_FOUND;

  END IF;
END;

--  create procedure to insert documents from confirmation templates
CREATE OR REPLACE PROCEDURE insert_document_conf_data (
    "BASE_DOC_INDEX" IN VARCHAR2,
    "DOC_NUMBER" IN "MDRU"."SND_DOCUMENTS"."DOC_NUMBER"%TYPE, "DOC_DATE" IN "MDRU"."SND_DOCUMENTS"."DOC_DATE"%TYPE,
    "DOC_KIND" IN "MDRU"."SND_DOCUMENTS"."DOC_KIND"%TYPE, "TMMASS" IN "MDRU"."SND_DOCUMENTS"."TMMASS"%TYPE,
    "BASE_DOC_ID" IN "MDRU"."SND_BASE_DOCS"."ID"%TYPE, "GOODS_ID" IN "MDRU"."SND_GOODS"."ID"%TYPE,
    "DOC_TYPE" IN "MDRU"."SND_DOCUMENTS"."DOC_TYPE"%TYPE
)
IS
BEGIN

  IF "BASE_DOC_INDEX"='Arrive' THEN
    INSERT INTO "MDRU"."RCV_DOCUMENTS"
      VALUES ("MDRU"."DOCUMENTS_ID_SEQUENCE".NEXTVAL, DOC_NUMBER, DOC_DATE,
              DOC_KIND, TMMASS, BASE_DOC_ID, GOODS_ID, DOC_TYPE);


  ELSEIF "BASE_DOC_INDEX"='Depart' THEN
    INSERT INTO "MDRU"."RCV_DOCUMENTS"
        VALUES ("MDRU"."DOCUMENTS_ID_SEQUENCE".NEXTVAL, DOC_NUMBER, DOC_DATE,
                DOC_KIND, TMMASS, BASE_DOC_ID, GOODS_ID, DOC_TYPE);

  ELSE
    RAISE CASE_NOT_FOUND;

  END IF;
END;

--  create procedure to insert transports from confirmation templates
CREATE OR REPLACE PROCEDURE insert_transport_conf_data (
    "BASE_DOC_INDEX" IN VARCHAR2, "BASE_DOC_ID" IN "MDRU"."SND_BASE_DOCS"."ID"%TYPE,
    "DEPARTURE_IDENTIFIER" IN "MDRU"."SND_TRANSPORTS"."DEPARTURE_IDENTIFIER"%TYPE,
    "CROSS_BORDER_IDENTIFIER" IN "MDRU"."SND_TRANSPORTS"."CROSS_BORDER_IDENTIFIER"%TYPE,
    "CROSS_BORDER_TMMAS" IN "MDRU"."SND_TRANSPORTS"."CROSS_BORDER_TMMAS"%TYPE
)
IS
BEGIN

  IF "BASE_DOC_INDEX"='Arrive' THEN
    INSERT INTO "MDRU"."RCV_TRANSPORTS"
      VALUES ("MDRU"."TRANSPORTS_ID_SEQUENCE".NEXTVAL, BASE_DOC_ID,
              DEPARTURE_IDENTIFIER, CROSS_BORDER_IDENTIFIER, CROSS_BORDER_TMMAS);


  ELSEIF "BASE_DOC_INDEX"='Depart' THEN
    INSERT INTO "MDRU"."SND_TRANSPORTS"
        VALUES ("MDRU"."TRANSPORTS_ID_SEQUENCE".NEXTVAL, BASE_DOC_ID,
                DEPARTURE_IDENTIFIER, CROSS_BORDER_IDENTIFIER, CROSS_BORDER_TMMAS);

  ELSE
    RAISE CASE_NOT_FOUND;

  END IF;
END;